package MaintMenu.PoAnalysis;

import MaintMenu.VenPricingUpdTBLN.delTblnCost;

import javax.swing.*;
import java.math.RoundingMode;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;

/**
 * Created with IntelliJ IDEA.
 * User: Jason
 * Date: 7/30/13
 * Time: 5:01 PM
 * To change this template use File | Settings | File Templates.
 */
public class VendorCostUpd {


    public VendorCostUpd(){

    }

    public VendorCostUpd(String cat,int updateBy){

//        System.out.println("Start "+cat+" Update");
        String cateroy=cat;
        String tbl="";
        String tblorg="";
        String tblPost="";
        String pfield="";

        //0 = PL ; 1=part#
        if (updateBy==0) {
            pfield="partlink";
            dnkNum="part#";

        }if (updateBy==1) {
            pfield="part#";
            dnkNum="part#";
        }

        //Categories
        if (cat=="BC") {
            tbl="TBLBCN_TEMPOUT2";
            tblorg="TBLBCN";

        }if (cat=="HF"){
            tbl="TBLHFN_TEMPOUT2";
            tblorg="TBLHFN";

        }if (cat=="OT"){
            tbl="TBLOTN_TEMPOUT2";
            tblorg="TBLOTN";

        }if (cat=="CL"){
            tbl="TBLCLN_TEMPOUT2";
            tblorg="TBLCLN";

        }if (cat=="MR"){
            tbl="TBLMRN_TEMPOUT2";
            tblorg="TBLMRN";

        }if (cat=="LTLI"){
            tbl="tblltn_TEMPOUTLI2";
            tblorg="TBLLTN";
//            tblPost="LI";
//            cat="LT";
        }if (cat=="LTBK"){
            tbl="tblltn_TEMPOUTBK2";
            tblorg="TBLLTN";
//            tblPost="BK";
//            cat="LT";
        }





//        System.out.println(notice);
        try {
            double cuftCost=2.5D;               //**** CUFT Cost $2.5 per cuft

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = "dbLI";
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection connection1 = DriverManager.getConnection(dbURL, "", "");
            Connection con = DriverManager.getConnection(dbURL, "", "");
            DatabaseMetaData md = connection1.getMetaData();
            Statement sta = con.createStatement();
            Statement sta1 = con.createStatement(); //forupdate LPP
            Statement sta2 = con.createStatement(); //Create Fields for +tbl+ LLP, stkval, stkvallpfob


//            System.out.println("1");
            try {

                ResultSet rs = md.getColumns(null, null, tbl, "LPP");
                if (rs.next()) {
                    //Column in table exist
//                    System.out.println("column exist");
                }else {
//                    System.out.println("column not exist");
                    sta2.execute("alter table "+tbl+" add LPP currency");
                    sta2.execute("alter table "+tbl+" add StkVal currency");
                    sta2.execute("alter table "+tbl+" add StkValLPPfob currency");
                }
                sta2.close();
                connection1.close();


//                System.out.println("pfield :"+pfield);
//                NumberFormat df = NumberFormat.getCurrencyInstance();
                NumberFormat df = DecimalFormat.getInstance();
                df.setMinimumFractionDigits(2);
                df.setMaximumFractionDigits(4);
                df.setRoundingMode(RoundingMode.DOWN);

                getVenField();
                CostCal bestVen = new CostCal();
                ValueCal stockVal = new ValueCal();
//                LLP lastPP = new LLP();          //***********
                Statement stupd = con.createStatement();

                //copy LPP from inv to tbl
                String updLPP ="UPDATE "+tbl+" as a INNER JOIN tblinventory as b on a.[part#]=b.[part#] SET a.lpp = b.lpp";
                sta1.execute(updLPP);


                String selecSta ="SELECT * FROM "+tbl+"";
//                String selecSta ="SELECT * FROM "+tbl+"";
//                System.out.println("Statement :"+selecSta);
                sta.execute(selecSta);

                ResultSet resultSet = sta.getResultSet();
                int rowupd =0;
//                System.out.println("2");
//                System.out.println(resultSet.getRow());

                if (resultSet != null)  {
//                    if (flash_CursorB==1){
//                        System.out.print("\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b");
//                        flash_CursorA=0;
//                        flash_CursorB=1;
//                    }
                    while (resultSet.next()) {
                        rowupd++;
//                    if (resultSet.next()) {
//                        if (!resultSet.next())
//                            break;

                        //update pricing based on value passed (part#, or partlink#)
                        String plNum = resultSet.getString(pfield);

                        if (updateBy==0) {

                            dnkNum=resultSet.getString("Part#");

                        }if (updateBy==1) {

                            dnkNum=plNum;
                        }



                        //to calcuate inventory value
//                        String pnum="Part#";
                        instock = resultSet.getInt("instock");      //******
                        //get LPP
                        lpp=resultSet.getDouble("lpp");
//                        dnkNum = resultSet.getString(pnum);      //get dnkNum;
//                        String dnkNum1 = resultSet.getString("Part#");      //get dnkNum;





//                        System.out.println("plNum :"+plNum);





                        if (cateroy=="BC"){
                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
                            tw = resultSet.getDouble(ty_p)+tw_shipCost;

                            tx = resultSet.getDouble(tx_p);

                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
                            gd = resultSet.getDouble(gd_p)+gd_shipCost;

                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
                            pt = resultSet.getDouble(pt_p)+pt_shipCost;


                        } if (cateroy=="HF"){
                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
                            tw = resultSet.getDouble(ty_p)+tw_shipCost;

//                            tx = resultSet.getDouble(tx_p);

                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
                            gd = resultSet.getDouble(gd_p)+gd_shipCost;

//                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
//                            pt = resultSet.getDouble(pt_p)+pt_shipCost;
//
//                            gn = resultSet.getDouble(gn_p);
//
//                            dp = resultSet.getDouble(dp_p);
//
//                            ks = resultSet.getDouble(ks_p);
//
//                            kv = resultSet.getDouble(kv_p);
//
//                            gn = resultSet.getDouble(gn_p);
                        } if (cateroy=="MR"){
                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
                            tw = resultSet.getDouble(ty_p)+tw_shipCost;

                            tx = resultSet.getDouble(tx_p);
//
//                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
//                            gd = resultSet.getDouble(gd_p)+gd_shipCost;
//
//                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
//                            pt = resultSet.getDouble(pt_p)+pt_shipCost;

                            gn = resultSet.getDouble(gn_p);

                            dp = resultSet.getDouble(dp_p);

//                            ks = resultSet.getDouble(ks_p);
//
                            kv = resultSet.getDouble(kv_p);

//                            gn = resultSet.getDouble(gn_p);

                        } if (cateroy=="CL"){

                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
                            tw = resultSet.getDouble(ty_p)+tw_shipCost;

                            tx = resultSet.getDouble(tx_p);

                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
                            gd = resultSet.getDouble(gd_p)+gd_shipCost;

//                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
//                            pt = resultSet.getDouble(pt_p)+pt_shipCost;

                            gn = resultSet.getDouble(gn_p);
//
                            dp = resultSet.getDouble(dp_p);
//
//                            ks = resultSet.getDouble(ks_p);
//
//                            kv = resultSet.getDouble(kv_p);
//
//                            gn = resultSet.getDouble(gn_p);
                        } if (cateroy=="LTLI" ||cateroy=="LTBK") {

//                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
//                            tw = resultSet.getDouble(ty_p)+tw_shipCost;
//
//                            tx = resultSet.getDouble(tx_p);
//
//                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
//                            gd = resultSet.getDouble(gd_p)+gd_shipCost;
//
//                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
//                            pt = resultSet.getDouble(pt_p)+pt_shipCost;

                            gn = resultSet.getDouble(gn_p);

                            dp = resultSet.getDouble(dp_p);

//                            ks = resultSet.getDouble(ks_p);
//
//                            kv = resultSet.getDouble(kv_p);
//
//                            gn = resultSet.getDouble(gn_p);
                        } if (cateroy=="OT"){

                            tw_shipCost = resultSet.getDouble(ty_c)*cuftCost;
                            tw = resultSet.getDouble(ty_p)+tw_shipCost;

//                            tx = resultSet.getDouble(tx_p);

                            gd_shipCost = resultSet.getDouble(gd_c)*cuftCost;
                            gd = resultSet.getDouble(gd_p)+gd_shipCost;

                            pt_shipCost = resultSet.getDouble(pt_c)*cuftCost;
                            pt = resultSet.getDouble(pt_p)+pt_shipCost;
//
//                            gn = resultSet.getDouble(gn_p);
//
//                            dp = resultSet.getDouble(dp_p);
//
//                            ks = resultSet.getDouble(ks_p);
//
//                            kv = resultSet.getDouble(kv_p);
//
//                            gn = resultSet.getDouble(gn_p);
                        }





//                        System.out.println("3");

                        try {
//                            supplier="";

                            //get last purchased price
//                            System.out.println("pass"+dnkNum);
//                            lpp= LLP(dnkNum);
//                            System.out.println("received"+lpp);
                            stkvallpp= lpp*instock;
//                            System.out.println("1");
//                            System.out.println(stkvallpp);


                            //get vendor with best price
                            supplier = bestVen.comparePrice(pt,dp,tx,ks,tw,gd,kv,gn);
//                            System.out.println("2");

                            //get a total of inventory value based on best price and instock count
                            stkval = stockVal.comparePrice(pt,dp,tx,ks,tw,gd,kv,gn,instock);
//                            System.out.println("3");




                            if (pfield=="partlink"){
//                                System.out.println("4");
                                String sql ="UPDATE "+ tbl +" SET supplier = '"+supplier+"', stkval="+df.format(stkval)+", stkvallppfob= "+df.format(stkvallpp)+" WHERE "+pfield+" = '"+plNum+"'";
//                                System.out.println(sql);
                                stupd.executeUpdate(sql);
                                if (cateroy=="BC"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="HF"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="MR"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+tx_p+" = "+tx+","+gn_p+" = "+gn+","+dp_p+" = "+dp+","+kv_p+" = "+kv+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="CL"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+tx_p+" = "+tx+","+gn_p+" = "+gn+","+dp_p+" = "+dp+", "+gd_p+" = "+gd+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="LTLI" ||cateroy=="LTBK") {
                                    String sql1 ="UPDATE "+ tbl +" SET "+gn_p+" = "+gn+", "+dp_p+" = "+dp+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="OT"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE "+pfield+"  = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                }
                                resetVariables();      //Cleanup all variables


//
//                                String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE "+pfield+" = '"+plNum+"'";
////                                System.out.println(sql1);
//                                stupd.executeUpdate(sql1);
//                                String sql2 ="UPDATE "+ tblorg +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE "+pfield+" = '"+plNum+"'";
////                                System.out.println(sql2);
//                                stupd.executeUpdate(sql2);
//                                System.out.print(plNum+"");
//                                System.out.println("sql execute1");                                                                      F

                            }else if (pfield=="part#"){
//                                System.out.println("5");
                                String sql ="UPDATE "+ tbl +" SET supplier = '"+supplier+"', stkval="+df.format(stkval)+", stkvallppfob= "+df.format(stkvallpp)+" WHERE ["+pfield+"] = '"+plNum+"'";
//                                System.out.println(sql);
                                stupd.executeUpdate(sql);

                                if (cateroy=="BC"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="HF"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="MR"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+tx_p+" = "+tx+","+gn_p+" = "+gn+","+dp_p+" = "+dp+","+kv_p+" = "+kv+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="CL"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+tx_p+" = "+tx+","+gn_p+" = "+gn+","+dp_p+" = "+dp+", "+gd_p+" = "+gd+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="LTLI" ||cateroy=="LTBK") {
                                    String sql1 ="UPDATE "+ tbl +" SET "+gn_p+" = "+gn+", "+dp_p+" = "+dp+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                } if (cateroy=="OT"){
                                    String sql1 ="UPDATE "+ tbl +" SET "+ty_p+" = "+tw+", "+gd_p+" = "+gd+","+pt_p+" = "+pt+" WHERE ["+pfield+"] = '"+plNum+"'";
                                    stupd.executeUpdate(sql1);
                                }
                                resetVariables();      //Cleanup all variables


                            }


                            if ( (rowupd % 7) == 1){
//                                System.out.print(".");
                            }if ((rowupd %  10) == 9 ){
                                System.out.print("\b");
                            }

                            resetVariables();
//                            System.out.println("Should be null :"+tw+" "+tx_p+" "+gn+" "+dp+" sup :"+supplier);

//                            if (flash_CursorA<75){
//                                System.out.print(".");
//                                flash_CursorA++;
//                                if (flash_CursorA==75){
//                                    flash_CursorB=75;
//                                }
//
//                            }if (flash_CursorB>=75){
//                                System.out.print("\b");
//                                flash_CursorB--;
//                                if (flash_CursorB==0){
//                                    flash_CursorA=0;
//                                }
//
//
//                            }
//                            if (flash_CursorA==0){
//                                System.out.print(".....................");
//                                flash_CursorA=1;
//                                flash_CursorB=1;
//                                }


//                                System.out.println("Total Record Updated :"+rowupd);
//                                stupd.executeUpdate(sql1);
//                                System.out.println("Total Record Updated :"+rowupd);
//                                System.out.println(sql);

                            //Update with PART#

//                                System.out.println(sql);

                            //                            st.execute((new StringBuilder()).append("update TBLBCN set REMARK = REMARK & ' ' & '").append(a).append("' WHERE [PART#] = '").append(b).append("';").toString());
                        } catch (SQLException er) {

                        }
                    }
                    sta.close();
                    sta1.close();
                    sta2.close();
                    con.close();
                    connection1.close();
                }
//                System.out.println("\nTotal "+cateroy+" Record Updated by ("+pfield+") :"+rowupd);
            }catch (SQLException er) {
                System.out.println(er);
            }

        }catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }


    }


    public void addField(String tbl1){
//        st.executeUpdate("ALTER TABLE "+ table +" ALTER COLUMN "+column+" "+newType+";");

        String s1="alter table "+tbl1+" add LPP currency";
        String s2="alter table "+tbl1+" add StkVal currency";
        String s3="alter table "+tbl1+" add StkValLPPfob currency";
    }
    //this method will change the field name of table based on the cat
    public void getVenField(){
//        if (cat=="BC" ||cat=="HF" || cat=="OT" || cat=="CL" || cat=="MR"|| cat=="LT") {

        ty_p="tyg_tw_p";
        ty_c="tyg_tw_c";
        tx_p="tyg_tx_p";
        tx_c="";
        gd_p="gd_p";
        gd_c="gd_c";
        pt_p="protec_p";
        pt_c="protec_c";
        gn_p="gn_p";
        gn_c="gn_c";
        dp_p="depo_p";
        dp_c="depo_c";
        ks_p="ks_p";
        ks_c="ks_c";
        kv_p="kv_p";
        kv_c="kv_c";
        ee_p="ee_p";
        ee_c="ee_c";
//        }

//        }if (cat=="HF"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";


//        }if (cat=="OT"){
//            ty_p="tyg_tw_p";
//            ty_c="tyg_tw_c";
//            tx_p="tyg_tx_p";
//            tx_c="";
//            gd_p="gd_p";
//            gd_c="gd_c";
//            pt_p="protec_p";
//            pt_c="protec_c";
//            gn_p="gn_p";           //not use
//            gn_c="gn_c";           //not use
//            dp_p="depo_p";         //not use
//            dp_c="depo_c";          //not use
//            ks_p="ks_p";            //not use
//            ks_c="ks_c";            //not use
//            kv_p="kv_p";            //not use
//            kv_c="kv_c";           //not use

//        }if (cat=="HF"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";

//        }if (cat=="CL"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";

//        }if (cat=="MR"){
//            ty_p="";
//            ty_c="";
//            tx_p="";
//            tx_c="";
//            gd_p="";
//            gd_c="";
//            pt_p="";
//            pt_c="";
//            gn_p="";
//            gn_c="";
//            dp_p="";
//            dp_c="";
//            ks_p="";
//            ks_c="";
//            kv_p="";
//            kv_c="";
//        }
    }

    public void resetVariables(){

//        supplier=" ";
        supplier="";
        lpp = 0.00D;
        dnkNum="";
        instock=0;
        stkval=0.00D;
        stkvallpp=0.00D;


        flash_CursorA=0;
        flash_CursorB=0;

        tw_shipCost = 0.00D;
        tw = 0.00D;

        tx = 0.00D;

        gd_shipCost = 0.00D;
        gd = 0.00D;

        pt_shipCost = 0.00D;
        pt = 0.00D;

        gn = 0.00D;

        dp = 0.00D;

        ks = 0.00D;

        kv = 0.00D;

        ee = 0.00D;
    }

    //Variables

    double lpp=0.00D;
    String dnkNum="";
    int instock=0;
    double stkval=0.00D;
    double stkvallpp=0.00D;

    String supplier=" ";


    int flash_CursorA=0;
    int flash_CursorB=0;

    double tw_shipCost = 0.00D;
    double tw = 0.00D;

    double tx = 0.00D;

    double gd_shipCost = 0.00D;
    double gd = 0.00D;

    double pt_shipCost = 0.00D;
    double pt = 0.00D;

    double gn = 0.00D;

    double dp = 0.00D;

    double ks = 0.00D;

    double kv = 0.00D;

    double ee = 0.00D;

    //  Variables maps to UA Database tbl??n_tempout2

    String ty_p="";
    String ty_c="";

    String tx_p="";
    String tx_c="";

    String gd_p="";
    String gd_c="";

    String pt_p="";
    String pt_c="";

    String gn_p="";
    String gn_c="";

    String dp_p="";
    String dp_c="";

    String ks_p="";
    String ks_c="";

    String kv_p="";
    String kv_c="";

    String ee_p="";
    String ee_c="";
}
